﻿/////////////////////////////////////////////////////////////////////////////////
//
// InstantCube
// - Automatically generates Analysis Services cubes
// (C) Copyright Johan Åhlén 2009
// 
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// any later version.
// 
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
// See the GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see http://www.gnu.org/licenses/.
//
// More info on InstantCube website: http://instantcube.codeplex.com
//
/////////////////////////////////////////////////////////////////////////////////


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AdvantIQ.InstantCube.Model;
using System.Data.OleDb;
using System.Globalization;

namespace AdvantIQ.InstantCube.Emitters
{
    public class SQLEmitter
    {
        public Project Project { get; set; }

        public SQLEmitter(Project project)
        {
            this.Project = project;
        }

        public void ExecuteSQL(string sqlCmd)
        {
            var conn = new OleDbConnection(Project.Connections.OLEDBConnection);
            try
            {
                var cmd = new OleDbCommand(sqlCmd, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public string EmitCreateDatabase()
        {
            var sb = new StringBuilder();
            sb.Append("IF (DB_ID('");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("') IS NULL)\r\n");
            sb.Append("  CREATE DATABASE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");
            return sb.ToString();
        }

        public string EmitDropDatabase()
        {
            var sb = new StringBuilder();
            sb.Append("IF (DB_ID('");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("') IS NOT NULL)\r\n");
            sb.Append("  DROP DATABASE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");
            return sb.ToString();
        }

        public string EmitFactTables()
        {
            var sb = new StringBuilder();
            sb.Append("USE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");

            foreach (var cube in Project.Cubes)
            {
                foreach (var mg in cube.MeasureGroups)
                {
                    sb.Append(EmitDropTable(mg.GetTableName()));
                    sb.Append(EmitTable(mg.GetFields(), mg.GetTableName(), mg.GetPrimaryKey()));
                }
            }
            return sb.ToString();
        }

        public string EmitDimensionTables()
        {
            var sb = new StringBuilder();
            sb.Append("USE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");

            foreach (var dimension in Project.Dimensions.Values)
            {
                sb.Append(EmitDropTable(dimension.GetTableName()));
                sb.Append(EmitTable(dimension.GetFields(), dimension.GetTableName(), dimension.GetPrimaryKey()));
            }
            return sb.ToString();
        }

        public string EmitSecurity(string ssasUsername)
        {
            var sb = new StringBuilder();
            sb.Append("USE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");

            sb.Append(string.Format("CREATE USER [{0}] FOR LOGIN [{0}]\r\n", ssasUsername));
            sb.Append(string.Format("ALTER USER [{0}] WITH DEFAULT_SCHEMA=[dbo]\r\n", ssasUsername));
            sb.Append(string.Format("EXEC sp_addrolemember N'db_datareader', N'{0}'\r\n", ssasUsername));
            return sb.ToString();
        }

        public string EmitForeignKeys()
        {
            var sb = new StringBuilder();
            sb.Append("USE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");

            foreach (var cube in Project.Cubes)
            {
                foreach (var mg in cube.MeasureGroups)
                {
                    foreach (var fk in mg.GetForeignKeys())
                    {
                        sb.Append("ALTER TABLE ");
                        sb.Append(mg.GetTableName());
                        sb.Append(" ADD CONSTRAINT ");
                        sb.Append(fk.ID);
                        sb.Append(" FOREIGN KEY(");
                        sb.Append(fk.LocalField);
                        sb.Append(") REFERENCES ");
                        sb.Append(fk.RemoteTable);
                        sb.Append("(");
                        sb.Append(fk.RemoteField);
                        sb.Append(")");
                        sb.Append("\r\n");
                    }
                }
            }
            return sb.ToString();
        }

        public string EmitDimensionData()
        {
            var sb = new StringBuilder();
            sb.Append("USE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");
            sb.Append("SET NOCOUNT ON;\r\n");

            foreach (var dimension in Project.Dimensions.Values)
            {
                foreach (var row in dimension.Values)
                {
                    sb.Append("INSERT INTO ");
                    sb.Append(dimension.GetTableName());
                    sb.Append("(");
                    sb.Append(EmitCommaSeparatedList(dimension.InsertFieldNames));
                    sb.Append(")\r\nVALUES (");
                    sb.Append(EmitCommaSeparatedList(row, str => "'" + str.Replace("'", "''") + "'"));
                    sb.Append(")\r\n");
                }
            }

            return sb.ToString();
        }

        public string EmitFactSampleData()
        {
            var sb = new StringBuilder();
            sb.Append("USE [");
            sb.Append(Project.Namings.SqlDbName);
            sb.Append("];\r\n");
            sb.Append("SET NOCOUNT ON;\r\n");

            var random = new Random();

            foreach (var cube in Project.Cubes)
            {
                foreach (var mg in cube.MeasureGroups)
                {
                    if (mg.SampleData == null)
                        continue;

                    for (int i = 0; i < mg.SampleData.Quantity; i++)
                    {
                        sb.Append("INSERT INTO ");
                        sb.Append(mg.GetTableName());
                        sb.Append("(");
                        sb.Append(EmitCommaSeparatedList(mg.GetWriteableFields()));
                        sb.Append(")\r\nVALUES (");

                        foreach (var measure in mg.Measures)
                        {
                            var sampleMeasure = mg.SampleData.Measures[measure.ID];

                            if (measure.Type.StartsWith("int") || measure.Type.StartsWith("bigint"))
                            {
                                int d = random.Next((int)(sampleMeasure.MaxValue - sampleMeasure.MinValue));
                                d = d + (int)sampleMeasure.MinValue;
                                sb.Append(d);
                            }
                            else
                            {
                                double d = random.NextDouble();
                                d = sampleMeasure.MinValue + (sampleMeasure.MaxValue - sampleMeasure.MinValue) * d;
                                var sampleValue = d.ToString("f2", CultureInfo.InvariantCulture.NumberFormat);
                                sb.Append(sampleValue);
                            }

                            sb.Append(", ");
                        }

                        var sep = "";
                        foreach (var mgDimension in mg.Dimensions)
                        {
                            sb.Append(sep);
                            sep = ", ";

                            var dimension = Project.Dimensions[mgDimension.ID];
                            if (dimension is TimeDimension)
                            {
                                int d = random.Next(dimension.Values.Count);
                                var row = dimension.Values[d];
                                var index = dimension.InsertFieldNames.ToList().IndexOf(dimension.GetPrimaryKey());
                                sb.Append(row[index]);
                            }
                            else
                            {
                                int d = random.Next(dimension.Values.Count) + 1;
                                sb.Append(d);
                            }
                        }

                        sb.Append(")\r\n");
                    }
                }
            }

            return sb.ToString();
        }

        private string EmitDropTable(string tableName)
        {
            var sb = new StringBuilder();
            sb.Append("IF OBJECT_ID('");
            sb.Append(tableName);
            sb.Append("', 'U') IS NOT NULL\r\n");
            sb.Append("  DROP TABLE ");
            sb.Append(tableName);
            sb.Append(";\r\n");
            return sb.ToString();
        }

        private string EmitTable(IEnumerable<FieldInfo> fields, string tableName, string primaryKey)
        {
            var sb = new StringBuilder();
            sb.Append("CREATE TABLE [");
            sb.Append(tableName);
            sb.Append("](\r\n");
            sb.Append(EmitFields(fields));
            sb.Append("  CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED\r\n");
            sb.Append("  (\r\n");
            sb.Append("    " + primaryKey + " ASC\r\n");
            sb.Append("  )\r\n);\r\n");
            return sb.ToString();
        }

        private string EmitFields(IEnumerable<FieldInfo> fields)
        {
            var sb = new StringBuilder();
            foreach (var field in fields)
            {
                sb.Append("  " + field.ID);
                sb.Append(" ");
                sb.Append(field.Type);
                if (!string.IsNullOrEmpty(field.Options))
                {
                    sb.Append(" ");
                    sb.Append(field.Options);
                }
                sb.Append(",\r\n");
            }
            return sb.ToString();
        }

        private string EmitCommaSeparatedList(IEnumerable<string> lst)
        {
            return EmitCommaSeparatedList(lst, str => str);
        }

        private string EmitCommaSeparatedList(IEnumerable<string> lst, Func<string,string> func)
        {
            var sb = new StringBuilder();
            var ch = "";

            foreach (var str in lst)
            {
                sb.Append(ch);
                ch = ", ";
                sb.Append(func(str));
            }

            return sb.ToString();
        }
    }
}
